package com.mbc.common.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.entities.BranchType;
import com.mbc.common.data.BranchTypeDataList;
import com.mbc.common.entities.BranchTypeRpt;

public class BranchTypeDAOImpls extends DAO<BranchType> implements
		BranchTypeDAO {

	public BranchTypeDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "BranchType";
	}

	@Override
	public void create(BranchType entity) throws SQLException {
		query = "insert into "
				+ table_name
				+ " ( BranchTypeId, BranchTypeCode, Name, Description, BranchID) values( ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getBranchTypeId());
		pstmt.setString(i++, entity.getBranchTypeCode());
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(BranchType entity) throws SQLException {
		query = "update " + table_name
				+ " set  Name = ?, Description = ?, BranchID = ?"
				+ " where BranchTypeId = " + entity.getBranchTypeId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getName());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchID());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where BranchTypeId = " + key;
		;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public BranchType find(long key) throws SQLException {
		BranchType l_BranchType = null;
		query = getSelectQuery() + " where BranchTypeId = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_BranchType = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_BranchType;
	}

	@Override
	public ArrayList<BranchType> findAll() throws SQLException {
		ArrayList<BranchType> l_BranchTypeList = new ArrayList<BranchType>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_BranchTypeList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_BranchTypeList;
	}

	@Override
	public BranchTypeDataList findBranchTypes(BranchType cri)
			throws SQLException {
		BranchTypeDataList l_DataList = new BranchTypeDataList();
		ArrayList<BranchType> l_entityList = new ArrayList<BranchType>();

		/*String l_cri = " and (BranchTypeCode like N'%" + cri.getKeyword()
		+ "%'" + " or Name like N'%" + cri.getKeyword() + "%'"
		+ " or Description like N'%" + cri.getKeyword() + "%')";*/

		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%')";

		query = "set rowcount "
				+ cri.getSize()
				+ " select * from (select row_number() over(order by BranchTypeId) as [rowno], * from "
				+ table_name + " where 1 = 1" + l_cri + ") " + table_name
				+ " where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_entityList.add(readRecord(rs));
		}
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(BranchTypeId) from " + table_name
				+ " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	public ResultSet prepareBranchTypeReport(BranchTypeRpt entity)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_BranchTypeRpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		BranchTypeRptParameter(l_pstmt, entity);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}

	private void BranchTypeRptParameter(CallableStatement l_pstmt,
			BranchTypeRpt entity) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, entity.getFromBranchTypeID());
		l_pstmt.setString(i++, entity.getToBranchTypeID());
		l_pstmt.setLong(i++, entity.getBranchID());
	}

	@Override
	protected BranchType readRecord(ResultSet rs) throws SQLException {
		BranchType l_entity = new BranchType();
		l_entity.setBranchTypeId(rs.getLong("BranchTypeId"));
		l_entity.setBranchTypeCode(rs.getString("BranchTypeCode"));
		l_entity.setName(rs.getString("Name"));
		l_entity.setDescription(rs.getString("Description"));
		l_entity.setBranchID(rs.getLong("BranchID"));
		return l_entity;
	}

	@Override
	public BranchType findBranchTypeByCode(BranchType entity)
			throws SQLException {
		BranchType l_BranchType = null;
		query = getSelectQuery() + " where 1 = 1";

		if (!entity.getBranchTypeCode().equals("")) {
			query += " and BranchTypeCode = '" + entity.getBranchTypeCode() + "'";
		}
		if (!entity.getName().equals("")) {
			query += " and Name = '" + entity.getName() + "'";
		}
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			l_BranchType = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_BranchType;

	}
}
